﻿Imports DevComponents
Imports TNCDAL
Imports TNCBAL
Imports Microsoft.Office.Interop
Imports HTLibrary

Public Class ucReceiptVouchersNew
    Dim db As New TanNamChinhDataContext
    Dim m_ID_User As Integer = GeneralClassBAL._User.ID_User
    Dim f_Them As Boolean = False
    Dim f_Sua As Boolean = False

    Private m_ID_File As Integer = 0
    Private m_ID_AdvanceVoucher As Integer = 0

    Private m_ID_CurrencyExchange As Integer
    Private m_LoaiPhieuThu As Integer = 0 ' 0: Thu binh thuong, 1: Thu khach hang, 2: Thu Tam Ung
    Private m_LoaiPhieuThuThemMoi As Integer = 0 '0: Thu binh thuong, 1: Thu khach hang, 2: Thu Tam Ung
    Private m_Position_dtgPhieuThu As Integer = 0
    Private m_ID_ReceiptVoucher As Integer = 0
    Private m_ID_HBL As Integer = 0

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        KhoiTaodtgPhieuThu()
        m_LoaiPhieuThu = 0
        KhoiTao_GiaoDien(True)
        DataGridViewHelper.DataGridView_InitEvent(dtgPhieuThu)
        DataGridViewHelper.DataGridView_InitEvent(dtgCTPhieuThu)
    End Sub

    Public Sub New(ByVal ID_HBL As Integer)

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        KhoiTaodtgPhieuThu()
        m_LoaiPhieuThuThemMoi = 1
        m_ID_HBL = ID_HBL

        'Kiem tra da lap phieu thu khach hang chua
        If m_ID_HBL > 0 Then
            Dim c_SellingFee = (From p In db.tblSellingFees
                            Where p.ID_HBL = ID_HBL And Not p.ID_ReceiptVoucher Is Nothing
                            Select p).Count

            If c_SellingFee > 0 Then
                Dim HBL = (From p In db.tblHBLs
                           Where p.ID_HBL = ID_HBL
                           Select p).Single

                'Tìm ID_ReceiptVoucher
                Dim ID_RV = (From p In db.tblSellingFees
                                        Where p.ID_HBL = HBL.ID_HBL
                                        Select p.ID_ReceiptVoucher).Distinct.SingleOrDefault

                'Find row where ID_AdvanceVoucher
                Dim i As Integer
                For i = 0 To dtgPhieuThu.RowCount - 1
                    If dtgPhieuThu.Rows(i).Cells("ID_ReceiptVoucher").Value = ID_RV Then
                        Exit For
                    End If
                Next
                dtgPhieuThu.Rows(i).Selected = True
                dtgPhieuThu.SelectedCells(4).Selected = True

                MsgBox("Đã lập phiếu thu cho HBL số: " + HBL.HBLNo)
                Exit Sub
            Else
                btnAdd_Click(Nothing, Nothing)
                txtTongTien.ReadOnly = True
                txtTongTien.BackColor = Color.Azure
                'KhoiTao_GiaoDien(False)
            End If
        End If
    End Sub

    Public Sub New(ByVal m_AdvanceVocher As tblAdvanceVoucher)
        ' Khoi tao phieu thu tam ung
        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        KhoiTaodtgPhieuThu()
        m_LoaiPhieuThuThemMoi = 2
        m_ID_AdvanceVoucher = m_AdvanceVocher.ID_AdvanceVoucher

        If m_AdvanceVocher.ID_ReceiptVoucher Is Nothing Then
            btnAdd_Click(Nothing, Nothing)
            'PanelEx3.Visible = False
            txtTongTien.ReadOnly = True
            txtTongTien.BackColor = Color.Azure
        Else
            'Tìm ID_ReceiptVoucher
            Dim ID_RV = m_AdvanceVocher.ID_ReceiptVoucher
            'Find row where ID_AdvanceVoucher
            Dim i As Integer
            For i = 0 To dtgPhieuThu.RowCount - 1
                If dtgPhieuThu.Rows(i).Cells("ID_ReceiptVoucher").Value = ID_RV Then
                    Exit For
                End If
            Next
            dtgPhieuThu.Rows(i).Selected = True
            dtgPhieuThu.SelectedCells(4).Selected = True
            MsgBox("Đã lập phiếu thu cho phiếu tạm ứng số: " + m_AdvanceVocher.AdvanceVoucherNo)
        End If
    End Sub

    Public Sub KhoiTaoPhieuThuMoi()
        ClearPhieuThu()

        'Khởi tạo số phiếu thu
        Dim SoPhieu As Integer = 0
        SoPhieu = (From p In db.tblReceiptVouchers
                  Select p.ReceiptVoucherNo).Max
        If SoPhieu + 1 < 10 Then
            txtSoPhieuThu.Text = "0" + (SoPhieu + 1).ToString + "/PT"
        Else
            txtSoPhieuThu.Text = (SoPhieu + 1).ToString + "/PT"
        End If

        Dim user = (From p In db.tblUsers
                   Where p.ID_User = m_ID_User
                   Select p).Single

        txtNguoiLapPhieu.Text = user.FullName

        'Khoi tao giao dien phiếu thu cua khách hàng
        If m_LoaiPhieuThuThemMoi = 1 Then
            Dim HBL = (From p In db.tblHBLs
                        Where p.ID_HBL = m_ID_HBL
                        Select p).Single

            'Get File
            Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

            If File.IsExport Then
                If File.FileNo > 10 Then
                    txtFile.Text = File.FileNo.ToString + "/" + "EX"
                Else
                    txtFile.Text = "0" + File.FileNo.ToString + "/" + "EX"
                End If
            Else
                If File.FileNo > 10 Then
                    txtFile.Text = File.FileNo.ToString + "/" + "IM"
                Else
                    txtFile.Text = "0" + File.FileNo.ToString + "/" + "IM"
                End If
            End If

            

            txtNoiDungThu.Text = "Thu File " + txtFile.Text + " - HBL số: " + HBL.HBLNo

            'Khoi tao chi tiet phieu thu khach hang

            Dim SellingFee = From p In db.tblSellingFees
                    Where p.ID_HBL = m_ID_HBL And p.Price > 0 And p.tblFee.IsActive = True
                    Order By p.tblFee.Rank
                    Select p.ID_HBL, p.ID_Fee, p.tblFee.FeeName, p.UnitPrice, p.Quantity, p.Price, p.ID_Currency, p.tblCurrency.Code, p.Description,
                    Ratio = (From q In db.tblHBLExchanges Where q.ID_HBL = m_ID_HBL And q.ID_Currency = p.ID_Currency Select q.Ratio).SingleOrDefault()



            dtgCTPhieuThu.DataSource = SellingFee
            FormatdtgCTPhieuThu()

            Dim TongTien As Double = 0
            For i As Integer = 0 To dtgCTPhieuThu.RowCount - 1
                Dim ID_Currency As Integer = 1
                ID_Currency = dtgCTPhieuThu.Rows(i).Cells("ID_Currency").Value
                If ID_Currency = 1 Then
                    TongTien += dtgCTPhieuThu.Rows(i).Cells("Price").Value
                Else
                    TongTien += dtgCTPhieuThu.Rows(i).Cells("Price").Value * dtgCTPhieuThu.Rows(i).Cells("Ratio").Value
                    If ID_Currency = 2 Then
                        txtTyGia.Text = CDbl(dtgCTPhieuThu.Rows(i).Cells("Ratio").Value).ToString("#,##0.##")
                    End If
                End If
            Next
            txtTongTien.Text = TongTien.ToString("#,##0.##")
        End If

        'Khoi tao giao dien phiếu thu của phiếu tạm ứng
        If m_LoaiPhieuThuThemMoi = 2 Then
            Dim av = (From p In db.tblAdvanceVouchers
                        Where p.ID_AdvanceVoucher = m_ID_AdvanceVoucher
                        Select p).Single

            txtSoPhieuTamUng.Text = av.AdvanceVoucherNo
            'Khoi tao noi dung phieu thu = phieu tam ung
            txtNoiDungThu.Text = "Thu phiếu tạm ứng số: " + av.AdvanceVoucherNo
            Dim TongTien As Double
            TongTien = av.Amount
            txtTongTien.Text = TongTien.ToString("#,##0.##")
        End If
    End Sub

    'Private Sub frmPhieuThu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    'End Sub

    Public Sub KhoiTaodtgPhieuThu(Optional ByVal indexRow As Integer = 0)
        'Lay danh sach file
        Dim rv = From p In db.tblReceiptVouchers
                   Select p.ID_ReceiptVoucher, ReceiptVoucherNo = p.ReceiptVoucherNo + "/PT", p.CreatedDate, p.Amount, p.Description, p.Payee, p.PayDay, p.IsApprove, Approver = p.tblUser2.FullName, p.ID_User, p.tblUser.FullName, p.ID_ApproveUser
                   Order By ReceiptVoucherNo Descending

        dtgPhieuThu.DataSource = rv

        If dtgPhieuThu.RowCount = 0 Then
            ClearPhieuThu()
        End If

        FormatdtgPhieuThu()

        Try
            dtgPhieuThu.Rows(indexRow).Selected = True
            'dtgPhieuThu.FirstDisplayedScrollingRowIndex = indexRow
            dtgPhieuThu.SelectedCells(5).Selected = True
        Catch ex As Exception

        End Try
    End Sub

    Public Sub FormatdtgPhieuThu()
        'dtgPhieuThu.RowHeadersWidth = 20
        dtgPhieuThu.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgPhieuThu.RowTemplate.Height = 25
        dtgPhieuThu.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgPhieuThu.MultiSelect = False
        'dtgPhieuThu.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgPhieuThu.ReadOnly = True
        dtgPhieuThu.BackgroundColor = Color.White
        dtgPhieuThu.AllowUserToAddRows = False
        dtgPhieuThu.AllowUserToDeleteRows = False

        dtgPhieuThu.Columns("ID_ReceiptVoucher").Visible = False
        'dtgPhieuThu.Columns("Description").Visible = False
        'dtgPhieuThu.Columns("Payee").Visible = False
        dtgPhieuThu.Columns("ID_User").Visible = False
        dtgPhieuThu.Columns("ID_ApproveUser").Visible = False
        dtgPhieuThu.Columns("IsApprove").Visible = False

        dtgPhieuThu.Columns("FullName").HeaderText = "CreatedUser"
        With dtgPhieuThu.Columns("ReceiptVoucherNo")
            .HeaderText = "ReceiptNo"
            '.Width = 80
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgPhieuThu.Columns("CreatedDate")
            '.HeaderText = "Ngày"
            '            .Width = 80
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgPhieuThu.Columns("Amount")
            '.HeaderText = "Số tiền"
            '.Width = 100
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
        With dtgPhieuThu.Columns("Description")
            '.HeaderText = "Nội dung thu"
            '.Width = 250
        End With
        With dtgPhieuThu.Columns("Payee")
            '.HeaderText = "Người nộp tiền"
            '.Width = 150
        End With
        With dtgPhieuThu.Columns("PayDay")
            '.HeaderText = "Ngày thanh toán"
            '.Width = 80
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
    End Sub

    Private Sub dtgPhieuThu_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtgPhieuThu.CurrentCellChanged
        If m_LoaiPhieuThuThemMoi > 0 Then
            Exit Sub
        End If
        Try
            'm_LoaiPhieuThu = 0
            Dim ID_PhieuThu As Integer = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value
            txtFile.Text = ""
            txtSoPhieuTamUng.Text = ""

            txtSoPhieuThu.Text = dtgPhieuThu.CurrentRow.Cells("ReceiptVoucherNo").Value.ToString
            dtpNgayPhieuThu.Value = dtgPhieuThu.CurrentRow.Cells("CreatedDate").Value
            txtNoiDungThu.Text = dtgPhieuThu.CurrentRow.Cells("Description").Value
            Try
                Dim TongTien As Double = 0
                TongTien = dtgPhieuThu.CurrentRow.Cells("Amount").Value
                txtTongTien.Text = TongTien.ToString("#,##0.##")
            Catch ex As Exception

            End Try

            txtNguoiNopTien.Text = dtgPhieuThu.CurrentRow.Cells("Payee").Value
            txtNguoiLapPhieu.Text = dtgPhieuThu.CurrentRow.Cells("FullName").Value

            Try
                dtpPayDay.Value = dtgPhieuThu.CurrentRow.Cells("PayDay").Value
            Catch ex As Exception
                dtpPayDay.Value = Nothing
            End Try


            Dim IsApprove As Boolean = False
            IsApprove = dtgPhieuThu.CurrentRow.Cells("IsApprove").Value
            If IsApprove Then
                lblNguoiXacNhan.Text = "Phiếu đã xác nhận" ' + dtgPhieuThu.CurrentRow.Cells("Approver").Value
            Else
                lblNguoiXacNhan.Text = ""
            End If


            KhoiTaodtgCTPhieuThu(ID_PhieuThu)

            'File No or AdvanceVoucher No
            '1. Kiem tra phieu thu khach hang hay khong?

            Dim c_sf = (From p In db.tblSellingFees
                      Where p.ID_ReceiptVoucher = ID_PhieuThu
                      Select p).Count

            If c_sf > 0 Then
                Dim sf = From p In db.tblSellingFees
                      Where p.ID_ReceiptVoucher = ID_PhieuThu
                      Select p

                Dim ID_HBL As Integer = 0

                For Each m_sf As tblSellingFee In sf
                    ID_HBL = m_sf.ID_HBL
                    Exit For
                Next

                Dim HBL = (From p In db.tblHBLs
                        Where p.ID_HBL = ID_HBL
                        Select p).Single

                'Get File
                Dim File = (From p In db.tblFiles
                           Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                           Select p).SingleOrDefault

                If File.IsExport Then
                    If File.FileNo > 10 Then
                        txtFile.Text = File.FileNo.ToString + "/" + "EX" + " - HBL No: " + HBL.HBLNo
                    Else
                        txtFile.Text = "0" + File.FileNo.ToString + "/" + "EX" + " - HBL No: " + HBL.HBLNo
                    End If
                Else
                    If File.FileNo > 10 Then
                        txtFile.Text = File.FileNo.ToString + "/" + "IM" + " - HBL No: " + HBL.HBLNo
                    Else
                        txtFile.Text = "0" + File.FileNo.ToString + "/" + "IM" + " - HBL No: " + HBL.HBLNo
                    End If
                End If

                'If HBL.Ratio Is Nothing Then
                '    txtTyGia.Text = 1
                '    cbbExchange.DataSource = Nothing
                'Else
                '    txtTyGia.Text = CDbl(HBL.Ratio).ToString("#,##0.##")
                Dim exchange = From p In db.tblHBLExchanges
                               Where p.ID_HBL = HBL.ID_HBL
                               Select p.ID_Currency, p.tblCurrency.Code, p.Ratio

                cbbExchange.DataSource = exchange
                cbbExchange.DisplayMember = "Code"
                cbbExchange.ValueMember = "Ratio"
                cbbExchange.SelectedIndex = 0
                'End If


                'm_LoaiPhieuThu = 1
            End If

            '2. Kiem tra phieu thu co phai la phieu thu tam ung hay khong?
            Dim c_av = (From p In db.tblAdvanceVouchers
                     Where p.ID_ReceiptVoucher = ID_PhieuThu
                     Select p).Count

            If c_av > 0 Then
                Dim av = (From p In db.tblAdvanceVouchers
                     Where p.ID_ReceiptVoucher = ID_PhieuThu
                     Select p).Single

                txtSoPhieuTamUng.Text = av.AdvanceVoucherNo
                txtTyGia.Text = 1
                cbbExchange.DataSource = Nothing
            End If
        Catch ex As Exception

        End Try
    End Sub

    Public Sub KhoiTaodtgCTPhieuThu(ByVal ID_PhieuThu As Integer)
        Dim SellingFee = From p In db.tblSellingFees
                      Where p.ID_ReceiptVoucher = ID_PhieuThu And p.UnitPrice > 0 And p.tblFee.IsActive = True
                      Order By p.tblFee.Rank
                      Select p.ID_HBL, p.ID_Fee, p.tblFee.FeeName, p.UnitPrice, p.Quantity, p.Price, p.tblCurrency.Code, p.ID_Currency, p.Description,
                      Ratio = (From q In db.tblHBLExchanges Where q.ID_HBL = m_ID_HBL And q.ID_Currency = p.ID_Currency Select q.Ratio).SingleOrDefault()

        dtgCTPhieuThu.DataSource = SellingFee
        FormatdtgCTPhieuThu()
    End Sub

    Public Sub FormatdtgCTPhieuThu()
        dtgCTPhieuThu.RowHeadersWidth = 20
        dtgCTPhieuThu.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgCTPhieuThu.RowTemplate.Height = 25

        dtgCTPhieuThu.MultiSelect = False
        dtgCTPhieuThu.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgCTPhieuThu.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells
        dtgCTPhieuThu.ReadOnly = True
        dtgCTPhieuThu.BackgroundColor = Color.White
        dtgCTPhieuThu.AllowUserToAddRows = False
        dtgCTPhieuThu.AllowUserToDeleteRows = False

        dtgCTPhieuThu.Columns("ID_HBL").Visible = False
        dtgCTPhieuThu.Columns("ID_Fee").Visible = False
        dtgCTPhieuThu.Columns("ID_Currency").Visible = False
        dtgCTPhieuThu.Columns("Ratio").Visible = False

        With dtgCTPhieuThu.Columns("Code")
            .HeaderText = "Curr"
            .Width = 60
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        End With

        With dtgCTPhieuThu.Columns("FeeName")
            .HeaderText = "Fee Name"
            .Width = 80
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        End With

        With dtgCTPhieuThu.Columns("Price")
            .HeaderText = "Price"
            .Width = 80
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With

        With dtgCTPhieuThu.Columns("UnitPrice")
            .HeaderText = "UnitPrice"
            .Width = 60
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
        With dtgCTPhieuThu.Columns("Quantity")
            .HeaderText = "Qty"
            .Width = 30
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
    End Sub

#Region "Them, Xoa, Sua"
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        f_Them = True
        

        If m_LoaiPhieuThuThemMoi = 0 Then
            Dim frm As New frmFile_PhieuTamUng
            frm.ShowDialog()
            '
            m_ID_AdvanceVoucher = frm.ID_AdvanceVoucher
            m_ID_HBL = frm.ID_HBL
            If m_ID_AdvanceVoucher > 0 Then
                m_LoaiPhieuThuThemMoi = 2
            End If
            If m_ID_HBL > 0 Then
                m_LoaiPhieuThuThemMoi = 1
            End If

            If m_ID_AdvanceVoucher = 0 And m_ID_HBL = 0 Then
                ' Khong chon
                Exit Sub
            End If
            txtNoiDungThu.Focus()
            ClearPhieuThu()
        End If

        KhoiTaoPhieuThuMoi()
        KhoiTao_GiaoDien(False)
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Try
            If dtgPhieuThu.CurrentRow.Cells("IsApprove").Value = True Then
                MsgBox("Phiếu thu này đã được xác nhận")
                Exit Sub
            End If

            'Kiểm tra người sử dụng
            If dtgPhieuThu.CurrentRow.Cells("ID_User").Value <> m_ID_User Then
                MsgBox("Bạn không phải người tạo phiếu thu này.")
                Exit Sub
            End If

        Catch ex As Exception

        End Try
        
        f_Sua = True
        KhoiTao_GiaoDien(False)
        m_Position_dtgPhieuThu = dtgPhieuThu.CurrentRow.Index
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Try
            If dtgPhieuThu.CurrentRow.Cells("IsApprove").Value = True Then
                MsgBox("Phiếu thu này đã được xác nhận")
                Exit Sub
            End If

            'Kiểm tra người sử dụng
            If dtgPhieuThu.CurrentRow.Cells("ID_User").Value <> m_ID_User Then
                MsgBox("Bạn không phải người tạo phiếu thu này.")
                Exit Sub
            End If
        Catch ex As Exception

        End Try

        Dim ID_PhieuThu As Integer
        ID_PhieuThu = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value
        Dim SoPhieuThu As String
        SoPhieuThu = dtgPhieuThu.CurrentRow.Cells("ReceiptVoucherNo").Value
        If MsgBox("Bạn muốn xóa phiếu thu số: " & SoPhieuThu, MsgBoxStyle.YesNo, "Chú ý") = MsgBoxResult.Yes Then
            'kiem tra neu la phieu thu khach hang
            'update ID_ReceiptVoucher = NULL
            Dim SellingFee = From p In db.tblSellingFees
                          Where p.ID_ReceiptVoucher = ID_PhieuThu
                          Select p

            For Each sf As tblSellingFee In SellingFee
                sf.ID_ReceiptVoucher = Nothing
            Next
            db.SubmitChanges()

            'Neu la phieu thu tam ung
            'Update(ID_ReceiptVoucher = NULL)
            Try
                Dim av = (From p In db.tblAdvanceVouchers
                     Where p.ID_ReceiptVoucher = ID_PhieuThu
                     Select p).Single

                av.ID_ReceiptVoucher = Nothing
                db.SubmitChanges()
            Catch ex As Exception

            End Try


            Dim rv = (From p In db.tblReceiptVouchers
                Where p.ID_ReceiptVoucher = ID_PhieuThu
                Select p).Single
            db.tblReceiptVouchers.DeleteOnSubmit(rv)
            db.SubmitChanges()

            'Refesh
            KhoiTaodtgPhieuThu()
        End If
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If f_Them Then
            If m_ID_HBL = 0 And m_ID_AdvanceVoucher = 0 Then
                MsgBox("Bạn chưa chọn thu tạm ứng hay thu khách hàng.")
                Exit Sub
            End If

            Dim rv As New tblReceiptVoucher
            Dim SoPhieu() As String
            SoPhieu = txtSoPhieuThu.Text.Split("/")
            rv.ReceiptVoucherNo = SoPhieu(0)
            rv.CreatedDate = dtpNgayPhieuThu.Value
            rv.Description = txtNoiDungThu.Text
            rv.Amount = txtTongTien.Text
            rv.Payee = txtNguoiNopTien.Text
            rv.ID_User = m_ID_User

            If dtpPayDay.Text = "" Then
                rv.PayDay = Nothing
            Else
                rv.PayDay = dtpPayDay.Value
            End If


            'kiem tra xem la thu khach hang hay thu tam ung
            '1. Thu khách hàng
            If m_LoaiPhieuThuThemMoi = 1 Then
                db.tblReceiptVouchers.InsertOnSubmit(rv)
                db.SubmitChanges()
                'update tblFileFee
                Dim SellingFee = From p In db.tblSellingFees
                        Where p.ID_HBL = m_ID_HBL
                        Select p

                For Each fee As tblSellingFee In SellingFee
                    fee.ID_ReceiptVoucher = rv.ID_ReceiptVoucher
                Next
                db.SubmitChanges()
            End If

            If m_LoaiPhieuThuThemMoi = 2 Then
                'Them ID_PhieuTamUng
                db.tblReceiptVouchers.InsertOnSubmit(rv)
                db.SubmitChanges()

                'update tblAdvanceVoucher
                Dim av = (From p In db.tblAdvanceVouchers
                         Where p.ID_AdvanceVoucher = m_ID_AdvanceVoucher
                         Select p).Single
                av.ID_ReceiptVoucher = rv.ID_ReceiptVoucher
                db.SubmitChanges()
            End If

            If m_ID_HBL = 0 And m_ID_AdvanceVoucher = 0 Then
                db.tblReceiptVouchers.InsertOnSubmit(rv)
                db.SubmitChanges()
            End If

            KhoiTaodtgPhieuThu()
        End If

        If f_Sua Then
            Dim ID_PhieuThu As Integer
            ID_PhieuThu = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value

            Dim rv = (From p In db.tblReceiptVouchers
                      Where p.ID_ReceiptVoucher = ID_PhieuThu
                      Select p).Single

            rv.CreatedDate = dtpNgayPhieuThu.Value
            rv.Description = txtNoiDungThu.Text
            'rv.Amount = txtTongTien.Text
            rv.Payee = txtNguoiNopTien.Text

            'Sỹ chưa kiểm tra là phiếu của mình hay không
            rv.ID_User = m_ID_User

            If dtpPayDay.Text = "" Then
                rv.PayDay = Nothing
            Else
                rv.PayDay = dtpPayDay.Value
            End If

            db.SubmitChanges()

            If m_ID_AdvanceVoucher > 0 Then
                db.SubmitChanges()
            End If

            Dim index As Integer = 0
            index = dtgPhieuThu.CurrentRow.Index
            KhoiTaodtgPhieuThu(index)
        End If

        f_Them = False
        f_Sua = False
        'Refesh
        KhoiTao_GiaoDien(True)
        m_LoaiPhieuThuThemMoi = 0
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        'refesh
        Dim index As Integer = 0
        index = dtgPhieuThu.CurrentRow.Index
        KhoiTaodtgPhieuThu(index)
        KhoiTao_GiaoDien(True)
        m_LoaiPhieuThuThemMoi = 0
    End Sub

    Public Sub ClearPhieuThu()
        dtgCTPhieuThu.DataSource = Nothing
        dtpNgayPhieuThu.Value = Now.Date
        txtFile.Text = ""
        txtSoPhieuTamUng.Text = ""
        txtNguoiLapPhieu.Text = ""
        txtNguoiNopTien.Text = ""
        txtNoiDungThu.Text = ""
        txtTongTien.Text = ""
        txtTyGia.Text = ""
        dtpPayDay.Value = Nothing
    End Sub

    Public Sub KhoiTao_GiaoDien(ByVal flag As Boolean)
        'set ReadOnly,visible,enable
        dtgPhieuThu.Enabled = flag

        btnAdd.Enabled = flag
        btnEdit.Enabled = flag
        btnDel.Enabled = flag
        btnUpdate.Enabled = Not flag
        btnCancel.Enabled = Not flag
        txtSoPhieuThu.ReadOnly = True
        txtNguoiLapPhieu.ReadOnly = True
        txtSoPhieuTamUng.ReadOnly = True
        txtFile.ReadOnly = True
        txtTyGia.ReadOnly = True
        txtTongTien.ReadOnly = True
        '
        dtpNgayPhieuThu.Enabled = Not flag
        dtpPayDay.Enabled = Not flag
        txtNoiDungThu.ReadOnly = flag
        txtNguoiNopTien.ReadOnly = flag

        ' ''set backcolor
        'txtSoPhieuThu.BackColor = Color.Azure
        'txtNguoiLapPhieu.BackColor = Color.Azure
        'txtSoPhieuTamUng.BackColor = Color.Azure
        'txtFile.BackColor = Color.Azure
        'txtTongTien.BackColor = Color.Azure
        'txtTyGia.BackColor = Color.Azure
        'cbbExchange.BackColor = Color.Azure

        'If (flag) Then
        '    txtNoiDungThu.BackColor = Color.Azure
        '    txtNguoiNopTien.BackColor = Color.Azure
        'Else
        '    txtNoiDungThu.BackColor = Color.White
        '    txtNguoiNopTien.BackColor = Color.White
        'End If
    End Sub
#End Region

    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        Dim ID_PhieuThu As Integer
        Try
            ID_PhieuThu = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value
        Catch ex As Exception
            ID_PhieuThu = dtgPhieuThu.Rows(0).Cells("ID_ReceiptVoucher").Value
        End Try

        Dim File As tblFile
        Dim FileNo As String = ""


        If m_LoaiPhieuThu = 0 Then
            'Thu bình thường

        ElseIf m_LoaiPhieuThu = 1 Then
            'Thu khách hàng
            Dim sf = From p In db.tblSellingFees
                      Where p.ID_ReceiptVoucher = ID_PhieuThu
                      Select p

            Dim ID_HBL As Integer = 0

            For Each m_sf As tblSellingFee In sf
                ID_HBL = m_sf.ID_HBL
                Exit For
            Next

            Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

            'Get File
            File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

            If File.IsExport Then
                If File.FileNo > 10 Then
                    FileNo = File.FileNo.ToString + "/" + "EX"
                Else
                    FileNo = "0" + File.FileNo.ToString + "/" + "EX"
                End If
            Else
                If File.FileNo > 10 Then
                    FileNo = File.FileNo.ToString + "/" + "IM"
                Else
                    FileNo = "0" + File.FileNo.ToString + "/" + "IM"
                End If
            End If
        ElseIf m_LoaiPhieuThu = 2 Then
            'Thu tạm ứng

        End If

        Dim report As New rptPhieuThu

        Dim pt = From p In db.viewS_PhieuThus
                 Where p.ID_ReceiptVoucher = ID_PhieuThu
                 Select p

        'Dim pt = From p In db.viewS_PhieuThus
        '         Where p.ID_ReceiptVoucher = ID_PhieuThu
        '         Select New With {p.ID_ReceiptVoucher,
        '                          p.ID_User,
        '                          p.CreatedDate,
        '                          p.ReceiptVoucherNo,
        '                          p.Description,
        '                          p.ID_Customer,
        '                          p.Payee,
        '                          p.Amount,
        '                          p.IsAdvance,
        '                          p.ID_SellingFee,
        '                          p.ID_Fee,
        '                          p.FeeName,
        '                          p.ID_FeeType,
        '                          p.FeeAbbreviated,
        '                          p.IsBill,
        '                          p.UnitPrice,
        '                          p.Quantity,
        '                          p.Price,
        '                          p.Expr1,
        '                          p.ID_Currency,
        '                          p.Code,
        '                          p.Name,
        '                          p.ID_HBL
        '                         }

        'MsgBox(pt.Count)

        Dim TongTien As Double = 0
        Dim ratio As Double = 1
        TongTien = dtgPhieuThu.CurrentRow.Cells("Amount").Value * ratio

        Dim BangChu As String = ""
        BangChu = CModule_Sy.NumToStringVietNam(TongTien)
        Dim dt As DataTable = CustomLINQtoDataSetMethods.CopyToDataTable(pt)
        report.SetDataSource(dt)
        'report.SetDataSource(HTLibrary.CollectionHelper.ConvertTo(pt.ToList))
        Dim NgayPT As Date = dtgPhieuThu.CurrentRow.Cells("CreatedDate").Value
        Dim NgayPT_BC As String
        NgayPT_BC = "Ngày " + NgayPT.Day.ToString + " tháng " + NgayPT.Month.ToString + " năm " + NgayPT.Year.ToString
        report.SetParameterValue("Ngay", NgayPT_BC)
        report.SetParameterValue("TongTien", TongTien)
        report.SetParameterValue("BangChu", BangChu)
        report.SetParameterValue("NguoiLapPhieu", txtNguoiLapPhieu.Text)
        report.SetParameterValue("loai", m_LoaiPhieuThu)
        If m_LoaiPhieuThu = 1 Then
            report.SetParameterValue("Ref", "Ref: " + txtFile.Text)
            report.SetParameterValue("Ratio", File.Ratio)
            report.SetParameterValue("DonVi", File.tblCompany.Name)
            Try
                report.SetParameterValue("DiaChi", File.tblCompany.Address)
            Catch ex As Exception
                report.SetParameterValue("DiaChi", "")
            End Try

            Try
                report.SetParameterValue("MST", File.tblCompany.TaxNo)
            Catch ex As Exception
                report.SetParameterValue("MST", "")
            End Try
        Else
            report.SetParameterValue("DonVi", "CTY TNHH DV & TM TÂN NAM CHINH")
            report.SetParameterValue("Ref", "")
            report.SetParameterValue("DiaChi", "")
            report.SetParameterValue("MST", "")
            report.SetParameterValue("Ratio", 1)
        End If

        Dim frm As New frmS_Report(report)
        frm.ShowDialog()
    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        ToEXCEL_PHIEUTHU()
    End Sub

    Private Sub ToEXCEL_PHIEUTHU()
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\PhieuThu_template.xltx")
        xlSheet = xlBook.Worksheets("Sheet1")
        xlApp.Visible = True



        Dim iRow0 As Integer = 11

        Dim ID_PhieuThu As Integer
        ID_PhieuThu = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value

        Dim obj_ReceiptVoucher = (From p In db.tblReceiptVouchers
                                 Where p.ID_ReceiptVoucher = ID_PhieuThu
                                 Select p).SingleOrDefault


        'NGÀY PHIẾU THU
        Dim DatePT As Date = obj_ReceiptVoucher.CreatedDate

        Dim strNgay As String = "Ngày " + DatePT.Day.ToString + " tháng " + DatePT.Month.ToString + " năm " + DatePT.Year.ToString

        xlSheet.Cells(5, 1) = strNgay

        'SỐ PHIẾU THU
        xlSheet.Cells(6, 8) = obj_ReceiptVoucher.ReceiptVoucherNo + "/PT"

        Dim i As Integer = 0
        Try
            'REF
            Dim sf = From p In db.tblSellingFees
                      Where p.ID_ReceiptVoucher = ID_PhieuThu And p.UnitPrice > 0
                      Select p

            Dim ID_HBL As Integer = 0

            For Each m_sf As tblSellingFee In sf
                ID_HBL = m_sf.ID_HBL
                Exit For
            Next


            Dim HBL = (From p In db.tblHBLs
                        Where p.ID_HBL = ID_HBL
                        Select p).Single


            'Get File
            Dim File = (From p In db.tblFiles
                           Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                           Select p).SingleOrDefault

            Dim Ref As String = ""
            If File.IsExport Then
                If File.FileNo > 10 Then
                    Ref = "EX" + File.FileNo.ToString
                Else
                    Ref = "EX" + "0" + File.FileNo.ToString
                End If
            Else
                If File.FileNo > 10 Then
                    Ref = "IM" + File.FileNo.ToString
                Else
                    Ref = "IM" + "0" + File.FileNo.ToString
                End If
            End If

            xlSheet.Cells(7, 8) = Ref


            'TÊN ĐƠN VỊ
            Dim m_Company As New CompanyRepository
            Dim obj_Company = m_Company.SelectRecordById(File.ID_Company)

            xlSheet.Cells(8, 3) = obj_Company.Name
            xlSheet.Cells(9, 3) = obj_Company.Address
            xlSheet.Cells(10, 3) = obj_Company.TaxNo

            'BILL

            Dim SellingFee = From p In db.tblSellingFees
                    Where p.ID_HBL = ID_HBL And p.Price > 0 And p.tblFee.IsActive = True
                    Order By p.tblFee.Rank
                    Select p.tblFee.FeeName, p.UnitPrice, p.Quantity, p.Price, p.ID_Currency, p.tblCurrency.Code, p.Description,
                    Ratio = (From q In db.tblHBLExchanges Where q.ID_HBL = ID_HBL And q.ID_Currency = p.ID_Currency Select q.Ratio).SingleOrDefault()

            For Each m_sf In SellingFee
                xlSheet.Cells(11 + i, 3) = m_sf.FeeName
                xlSheet.Cells(11 + i, 4) = m_sf.Price
                xlSheet.Cells(11 + i, 5) = m_sf.Code
                Dim Ratio As Double = 0

                If m_sf.ID_Currency = 1 Then
                    xlSheet.Cells(11 + i, 6) = m_sf.Price
                Else
                    xlSheet.Cells(11 + i, 6) = m_sf.Price * m_sf.Ratio
                End If
                i += 1
            Next
        Catch ex As Exception
            xlSheet.Cells(8, 3) = "CTY TNHH DV & TM TÂN NAM CHINH"
        End Try
        

        'TỔNG CỘNG
        xlSheet.Cells(11 + i, 1) = "TỔNG "
        xlSheet.Cells(11 + i, 2) = ":"
        xlSheet.Cells(11 + i, 6) = txtTongTien.Text
        xlSheet.Cells(11 + i, 7) = "VND"
        mRange = xlSheet.Range(xlSheet.Cells(11 + i, 3), xlSheet.Cells(11 + i, 9))
        mRange.Font.Bold = True

        'BẰNG CHỮ
        xlSheet.Cells(11 + i + 1, 1) = "BẰNG CHỮ "
        xlSheet.Cells(11 + i + 1, 2) = ":"
        xlSheet.Cells(11 + i + 1, 3) = CModule_Sy.NumToStringVietNam(CDbl(txtTongTien.Text))

        'NỘI DUNG
        xlSheet.Cells(11 + i + 2, 1) = "NỘI DUNG "
        xlSheet.Cells(11 + i + 2, 2) = ":"
        xlSheet.Cells(11 + i + 2, 3) = txtNoiDungThu.Text


        'ĐUÔI PHIẾU
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 1), xlSheet.Cells(11 + i + 4, 9))
        mRange.Font.Bold = True
        mRange.Font.Size = 11
        mRange.Font.Italic = True
        'NGƯỜI LẬP PHIẾU


        xlSheet.Cells(11 + i + 4, 1) = "NGƯỜI LẬP PHIẾU "
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 1), xlSheet.Cells(11 + i + 4, 3))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        xlSheet.Cells(11 + i + 4, 4) = "THỦ QUỸ"
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 4), xlSheet.Cells(11 + i + 4, 5))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        xlSheet.Cells(11 + i + 4, 7) = "NGƯỜI NỘP TIỀN"
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 7), xlSheet.Cells(11 + i + 4, 9))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter


        'HỌ TÊN
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 1), xlSheet.Cells(11 + i + 8, 9))
        mRange.Font.Bold = True
        mRange.Font.Size = 11

        xlSheet.Cells(11 + i + 8, 1) = txtNguoiLapPhieu.Text.ToUpper
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 1), xlSheet.Cells(11 + i + 8, 3))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        xlSheet.Cells(11 + i + 8, 4) = "THỦ QUỸ"
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 4), xlSheet.Cells(11 + i + 8, 5))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        xlSheet.Cells(11 + i + 8, 7) = txtNguoiNopTien.Text.ToUpper
        mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 7), xlSheet.Cells(11 + i + 8, 9))
        mRange.Merge()
        mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        'FORMAT GẠCH DƯỚI
        For j As Integer = 8 To 13 + i
            mRange = xlSheet.Range(xlSheet.Cells(j, 3), xlSheet.Cells(j, 9))
            With mRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = Excel.XlLineStyle.xlDot
                .Weight = Excel.XlBorderWeight.xlHairline
            End With
        Next
    End Sub

    Private Sub txtTongTien_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
        Try
            Dim TongTien As Double = 0
            TongTien = txtTongTien.Text
            txtTongTien.Text = TongTien.ToString("#,###")
        Catch ex As Exception

        End Try
    End Sub

    Private Sub dtgPhieuThu_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgPhieuThu.DataBindingComplete
        For i As Integer = 0 To dtgPhieuThu.RowCount - 1
            Try
                If dtgPhieuThu.Rows(i).Cells("PayDay").Value Is Nothing Then
                    dtgPhieuThu.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                End If

                If dtgPhieuThu.Rows(i).Cells("IsApprove").Value = True Then
                    dtgPhieuThu.Rows(i).DefaultCellStyle.ForeColor = Color.Green
                End If
            Catch ex As Exception

            End Try


        Next
    End Sub

    Private Sub cbbExchange_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbbExchange.SelectedIndexChanged
        Try
            txtTyGia.Text = CDbl(cbbExchange.SelectedValue).ToString("#,##0.##")
        Catch ex As Exception

        End Try

    End Sub

    Private Sub XácNhậnToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles XácNhậnToolStripMenuItem.Click
        'Kiểm tra phiếu đã xác nhận chưa
        If dtgPhieuThu.CurrentRow.Cells("IsApprove").Value = False Then
            Dim ID_ReceiptVoucher As Integer
            ID_ReceiptVoucher = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value
            Dim m_RV As New CReceiptVoucher
            Dim obj_RV As New tblReceiptVoucher
            obj_RV.ID_ReceiptVoucher = ID_ReceiptVoucher
            obj_RV.IsApprove = True
            obj_RV.ID_ApproveUser = m_ID_User
            m_RV.UpdateApprove(obj_RV)

            KhoiTaodtgPhieuThu(dtgPhieuThu.CurrentRow.Index)

            ''Khoi tao dtgReceiptVoucher
            'Dim ID_File As Integer = dtgPhieuThu.CurrentRow.Cells("ID_File").Value
            'KhoiTaoReceiptVoucher(ID_File)
        End If
    End Sub

    Private Sub HủyXácNhậnToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HủyXácNhậnToolStripMenuItem.Click
        'Kiểm tra có trùng người xác nhận không? Admin, Giám đốc 1, giám đốc 2, ...
        If dtgPhieuThu.CurrentRow.Cells("IsApprove").Value = True Then
            If dtgPhieuThu.CurrentRow.Cells("Id_ApproveUser").Value = m_ID_User Then
                Dim ID_ReceiptVoucher As Integer
                ID_ReceiptVoucher = dtgPhieuThu.CurrentRow.Cells("ID_ReceiptVoucher").Value
                Dim m_RV As New CReceiptVoucher
                Dim obj_RV As New tblReceiptVoucher
                obj_RV.ID_ReceiptVoucher = ID_ReceiptVoucher
                obj_RV.IsApprove = False
                obj_RV.ID_ApproveUser = Nothing
                m_RV.UpdateApprove(obj_RV)

                KhoiTaodtgPhieuThu(dtgPhieuThu.CurrentRow.Index)
            Else
                MsgBox("Bạn không phải là người xác nhận phiếu")
            End If
        End If
    End Sub
End Class
